const query = require("../config/db")
const express = require("express")
const router = express.Router()

//同步课程的相关接口
router.get("/getArea", async (req, res, next) => {
    //查询所有的地区
    let sql = `SELECT * FROM e_area`;
    let result = await query(sql);
    //给前端
    res.send({
        success: true,
        msg: "查询成功",
        status: 200,
        data: result
    })
})

//同步课程-年级数据
router.get("/getGrade", async (req, res, next) => {
    let sql = `SELECT * FROM e_grade`;
    let result = await query(sql);
    //给前端
    res.send({
        success: true,
        msg: "查询成功",
        status: 200,
        data: result
    })
})
//同步课程-学科数据 ??????????????????????????????/
router.get("/getSubject", () => {

})


//同步课程-考试类名 ??????????????????????????????/
router.get("/getCate", () => {

})

//同步课程-课程接口 
/*
分页逻辑
-- 36(总条数)条数据 一页10（一页的数量）条数据，一共有4页；
-- 总页数： Math.ceil(总条数/一页的数量)


-- limit a,b  第1页 0,10   第2页 10,10  第3页 20,10 第4页 30,10
-- 第page页,size条  (page-1)*size,size

SELECT * FROM e_course LIMIT 30,10
*/

// 请求参数：页码[page],一页的数量[size]
// 响应数据：总页数，总条数，数据，当前的页码
/*
router.get("/course", async (req, res, next) => {
    //接收前端传递过来的page和size
    let { page = 1, size = 10 } = req.query;

    //将page和size转换为number
    page = Number(page)
    size = Number(size)

    //查询所有的数据
    let sql1 = `select * from e_course`;
    let result1 = await query(sql1);//所有的课程数据 36

    //总页数 
    let totalPage = Math.ceil(result1.length / size)

    //总条数
    let totalNum = result1.length;

    //第page页的size条数据 
    let sql2 = `SELECT * FROM e_course LIMIT ${(page - 1) * size},${size}`;
    let result2 = await query(sql2)

    //返回给前端
    res.send({
        success: true,
        msg: "查询成功",
        status: 200,
        data: {
            //总页数
            totalPage,
            // 总条数
            totalNum,
            // 数据
            list: result2,
            // 当前页码
            page
        }
    })


})*/

//添加筛选条件
router.get("/course", async (req, res, next) => {
    //接收前端传递过来的page和size area[地区]、grade[年级] 、subject[学科]、cate[类别]
    let { page = 1, size = 10, area = "", grade = "", subject = "", cate = "" } = req.query;

    // where语句
    let whereStr = ""
    // 地区
    if (whereStr === '') {
        if (area !== '') {
            whereStr += ` where area_name='${area}'`
        }
    } else {
        if (area !== '') {
            whereStr += ` and area_name='${area}'`
        }
    }

    //年级
    // 先判断whereStr是否为空，如果是空，就是用where拼接；如果不是，就使用and拼接
    if (whereStr === '') {
        if (grade !== '') {
            whereStr += ` where grade_name='${grade}'`
        }
    } else {
        if (grade !== '') {
            whereStr += ` and grade_name='${grade}'`
        }
    }

    //学科
    if (whereStr === '') {
        if (subject !== '') {
            whereStr += ` where subject_name='${subject}'`
        }
    } else {
        if (subject !== '') {
            whereStr += ` and subject_name='${subject}'`
        }
    }
    //类别
    if (whereStr === '') {
        if (cate !== '') {
            whereStr += ` where category_name='${cate}'`
        }
    } else {
        if (cate !== '') {
            whereStr += ` and category_name='${cate}'`
        }
    }

    //将page和size转换为number
    page = Number(page)
    size = Number(size)

    //查询所有的数据
    let sql1 = `select * from e_course${whereStr}`;
    let result1 = await query(sql1);//所有的课程数据 36

    //总页数 
    let totalPage = Math.ceil(result1.length / size)

    //总条数
    let totalNum = result1.length;

    //第page页的size条数据 
    let sql2 = `SELECT * FROM e_course${whereStr} LIMIT ${(page - 1) * size},${size}`;

    let result2 = await query(sql2)

    //返回给前端
    res.send({
        success: true,
        msg: "查询成功",
        status: 200,
        data: {
            //总页数
            totalPage,
            // 总条数
            totalNum,
            // 数据
            list: result2,
            // 当前页码
            page
        }
    })


})



module.exports = router;